package com.personal.excel.analyzer;

import java.io.InputStream;
import java.util.Collections;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.personal.core.data.DataColumn;
import com.personal.core.data.DataRow;
import com.personal.core.data.DataTables;
import com.personal.core.data.DataTable;
import com.personal.core.data.IrregularData;
import com.personal.core.htmldata.HtmlIrregularData;
import com.personal.core.utils.Assert;
import com.personal.core.utils.CoreUtil;
import com.personal.core.utils.ReGularUtil;
import com.personal.dataconvert.bean.HeaderConfig;
import com.personal.dataconvert.util.ExcelHtmlUtil;
import com.personal.excel.config.SheetRule;
import com.personal.excel.config.SheetRuleParser;

import net.sf.json.JSON;

/**
 * 规则Excel解析
 * 
 * @author cuibo
 */
public class RegularExcelAnalyzer {

    /**
     * 解析Excel
     * 
     * @param in
     * @param rule
     * @return
     * @throws Exception
     */
    public static DataTables analyseExcel(InputStream in, InputStream rule) throws Exception {
        Assert.isNotNull(in, "导入的文件流不能为空！");
        Assert.isNotNull(rule, "导入的规则流不能为空！");
        return analyseExcel(in, SheetRuleParser.parseXml(rule));
    }

    public static void main(String[] args) {
        try {
            SheetRule sheetRule = new SheetRule();
            sheetRule.setSheetIndex(0);
            sheetRule.setHeadStart(0);
            sheetRule.setHeadEnd(1);
            DataTables tables = RegularExcelAnalyzer.analyseExcel(RegularExcelAnalyzer.class.getResourceAsStream("接口1.xlsx"),
                                                                  Collections.singletonList(sheetRule));
            for (DataRow row : tables.get(0).getRows()) {
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 解析Excel
     * 
     * @param in
     * @param rules
     * @return
     * @throws Exception
     */
    public static DataTables analyseExcel(InputStream in, List<SheetRule> rules) throws Exception {
        Assert.isNotNull(in, "导入的文件流不能为空！");
        Assert.isNotNullOrEmpty(rules, "规则配置不能为空！");
        Workbook book = ExcelHtmlUtil.getWorkbook(in);
        if (book == null) {
            return null;
        }
        DataTables result = new DataTables();
        for (SheetRule rule : rules) {
            DataTable table = analyseSingleSheet(book, rule);
            if (table != null) {
                result.add(table);
            }
        }
        return result;
    }

    /**
     * 解析单个Sheet页
     * 
     * @param book
     * @param rule
     * @return
     */
    private static DataTable analyseSingleSheet(Workbook book, SheetRule rule) {
        Sheet sheet = null;
        if (rule.getSheetIndex() > -1) {
            sheet = book.getSheetAt(rule.getSheetIndex());
        } else {
            sheet = book.getSheet(rule.getSheetName());
        }
        if (sheet == null) {
            return null;
        }
        if (rule.isRegular()) {
            return analyseRegularSheet(sheet, rule);
        } else {
            return analyseIRegularSheet(sheet, rule);
        }
    }

    private static DataTable analyseRegularSheet(Sheet sheet, SheetRule rule) {
        // 获取表头行
        DataTable result = new DataTable(sheet.getSheetName());
        analyseRegularSheetDataColumn(result, sheet, rule);
        fillRegularSheetDataRow(result, sheet, rule);
        return result;
    }

    private static DataTable analyseIRegularSheet(Sheet sheet, SheetRule rule) {
        DataTable result = new DataTable(sheet.getSheetName());
        result.initIrregularDataColumns();
        List<HtmlIrregularData> datas = IrregularExcelAnalyzer.analyseSheet(sheet);
        if (CoreUtil.isEmpty(datas)) {
            return result;
        }
        for (HtmlIrregularData irregularData : datas) {
            DataRow newRow = result.newRow();
            newRow.setValue(IrregularData.DATA, irregularData.getData());
            newRow.setValue(IrregularData.COLINDEX, irregularData.getColIndex());
            newRow.setValue(IrregularData.ROWINDEX, irregularData.getRowIndex());
            newRow.setValue(IrregularData.COLSPAN, irregularData.getColSpan());
            newRow.setValue(IrregularData.ROWSPAN, irregularData.getRowSpan());
            result.getRows().add(newRow);
        }
        return result;
    }

    private static void fillRegularSheetDataRow(DataTable result, Sheet sheet, SheetRule rule) {
        for (int i = rule.getHeadEnd(); i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            // 判断是否过滤空行
            if (rule.isFilterNullRow()) {
                boolean isEmpty = true;
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        if (!CoreUtil.isEmpty(ExcelHtmlUtil.getCellStringValue(cell))) {
                            isEmpty = false;
                            break;
                        }
                    }
                }
                if (isEmpty) {
                    continue;
                }
            }
            DataRow dataRow = result.newRow();
            fillDataRowWithExcelRow(row, dataRow, sheet);
            result.getRows().add(dataRow);
        }
    }

    private static void fillDataRowWithExcelRow(Row row, DataRow dataRow, Sheet sheet) {
        if (row == null) {
            return;
        }
        for (int i = 0; i < row.getLastCellNum(); i++) {
            String value = "";
            Cell cell = row.getCell(i);
            if (cell != null) {
                value = ExcelHtmlUtil.getCellStringValue(cell);
                if (i < dataRow.getDataTable().getColumns().size()) {
                    String columnName = ((DataColumn) dataRow.getDataTable().getColumns().get(i)).getColumnName();
                    dataRow.setValue(columnName, value);
                }
            }
        }
    }

    private static void analyseRegularSheetDataColumn(DataTable result, Sheet sheet, SheetRule rule) {
        List<Row> rows = ExcelHtmlUtil.getHeadNotEmptyRowNums(rule.getHeadStart(), rule.getHeadEnd(), sheet);
        if ((rows == null) || (rows.size() < 1)) {
            return;
        }
        Row head = (Row) rows.get(0);
        // 样式表头：如果没有数据行，则使用最后一个表头行
        Row style = sheet.getRow(rule.getHeadEnd());
        style = style == null ? rows.get(rows.size() - 1) : style;
        if (rule.getHeadStart() == rule.getHeadEnd() + 1) {
            // 单层表头
            setHeadColumn(rule, head, style, result);
        } else {
            for (Row row : rows) {
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        String value = ExcelHtmlUtil.getCellStringValue(cell);

                        int rowIndex = cell.getRowIndex();
                        if (ExcelHtmlUtil.isMergedRegion(sheet, cell)) {
                            int count = getCellColspan(rowIndex, i, sheet);
                            if (count > 0) {
                                for (int j = 0; j < count - 1; j++) {
                                    Cell cell2 = row.getCell(i + j + 1);
                                    if (cell2 != null) {
                                        cell2.setCellValue(value);
                                    }
                                }
                                i += count - 1;
                            }
                        }
                    }
                }
            }
            for (int j = 1; j < rows.size(); j++) {
                Row row = (Row) rows.get(j);
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);

                    if (cell != null) {
                        if (!CoreUtil.isEmpty(ExcelHtmlUtil.getCellStringValue(cell))) {
                            String cellValue = ExcelHtmlUtil.getCellStringValue(head.getCell(i));

                            String title = cellValue + rule.getMultiSplit() + ExcelHtmlUtil.getCellStringValue(cell);

                            Cell headCell = head.getCell(i);

                            if (headCell != null) {
                                headCell.setCellValue(title);
                            }
                        }
                    }
                }
            }
            setHeadColumn(rule, head, style, result);
        }
    }

    private static void setHeadColumn(SheetRule rule, Row head, Row style, DataTable table) {
        if (table == null) {
            return;
        }
        if (head == null) {
            return;
        }
        table.getColumns().clear();
        for (int i = 0; i < head.getLastCellNum(); i++) {
            Cell cell = head.getCell(i);
            if (cell != null) {
                String value = ExcelHtmlUtil.getCellStringValue(cell);
                if (!CoreUtil.isEmpty(value)) {
                    value = ReGularUtil.NULLPATTERN.matcher(value).replaceAll("");
                }
                DataColumn dataColumn = new DataColumn(value);
                dataColumn.setWidth((int) head.getSheet().getColumnWidthInPixels(i));
                Cell styleCell = style.getCell(i);
                if (styleCell == null) {
                    styleCell = head.getCell(i);
                }
                if (styleCell != null) {
                    dataColumn.setAlign(convertAlign(styleCell.getCellStyle().getAlignment()));
                }
                table.getColumns().add(dataColumn);
            }
        }
    }

    private static String convertAlign(short alignment) {
        switch (alignment) {
            case CellStyle.ALIGN_LEFT:
                return HeaderConfig.LEFT;
            case CellStyle.ALIGN_CENTER:
                return HeaderConfig.CENTER;
            case CellStyle.ALIGN_RIGHT:
                return HeaderConfig.RIGHT;
            default:
                break;
        }
        return HeaderConfig.CENTER;
    }

    private static int getCellColspan(int rowIndex, int colIndex, Sheet sheet) {
        int sheetmergerCount = sheet.getNumMergedRegions();

        int colspan = 0;

        for (int i = sheetmergerCount - 1; i >= 0; i--) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            if (ca != null) {
                int firstC = ca.getFirstColumn();
                int lastC = ca.getLastColumn();
                int firstR = ca.getFirstRow();

                if ((firstC == colIndex) && (firstR == rowIndex)) {
                    colspan = lastC - firstC + 1;
                    break;
                }
            }
        }
        return colspan;
    }

}
